package com.tony.jdbctemplate.dao.impl;

import com.alibaba.fastjson2.JSONObject;
import com.tony.jdbctemplate.dao.StudentDao;
import com.tony.jdbctemplate.entity.Student;
import com.tony.jdbctemplate.mapper.StudentMapper;
import jakarta.annotation.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SimplePropertySqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * create by xuxia on 2024/2/4 09:19
 */
@Repository
public class StudentDaoImpl implements StudentDao {

    @Resource
    private JdbcTemplate template;

    @Override
    public int add(Student student) {
        //String sql = "insert into student(s_no, sname, ssex) values(?,?,?)";
        //return template.update(sql, student.getSno(), student.getName(), student.getSex());
        String sql = "insert into student(s_no, sname, ssex) values(:sno,:name,:sex)";
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource()));
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(student);
        return namedParameterJdbcTemplate.update(sql, beanPropertySqlParameterSource);
    }

    @Override
    public int update(Student student) {
        //String updateSql = "update student set sname=?,ssex=? where s_no=?";
        //return template.update(updateSql, student.getName(), student.getSex(), student.getSno());
        String updateSql = "update student set sname= :name,ssex= :sex where s_no= :sno";
        HashMap<String, Object> map = new HashMap<>();
        map.put("name", student.getName());
        map.put("sex", student.getSex());
        map.put("sno", student.getSno());
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValues(map);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource()));
        return namedParameterJdbcTemplate.update(updateSql, mapSqlParameterSource);
    }

    @Override
    public int deleteBySno(String sno) {
        String s = "delete from student where s_no= :sno";
        Map<String, String> map = new HashMap<>();
        map.put("sno", sno);
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource(map);
        return new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource())).update(s, mapSqlParameterSource);
    }

    @Override
    public List<Map<String, Object>> queryStudentsListMap() {
        String sql = "select * from student";
        return template.queryForList(sql);
    }

    @Override
    public List<Student> queryAllStudents() {
        String sql = "select * from student";
        //PreparedStatementCreator preparedStatementCreator = con -> con.prepareStatement(sql);
        //return template.query(preparedStatementCreator, new StudentMapper());
        return template.query(con -> con.prepareStatement(sql), new StudentMapper());
    }

    @Override
    public Student queryStudentBySnoWithSimple(String sno) {
        String sql = "select * from student where s_no = :sno";
        // JSONObject jsonObject = new JSONObject();
        // jsonObject.put("sno", sno);
        Student student = new Student();
        student.setSno(sno);
        SimplePropertySqlParameterSource sqlParameterSource = new SimplePropertySqlParameterSource(student);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource()));
        List<Student> students = namedParameterJdbcTemplate.query(sql, sqlParameterSource, new StudentMapper());
        if (CollectionUtils.isEmpty(students)) {
            return null;
        } else {
            return students.get(0);
        }
    }

    @Override
    public Student queryStudentBySnoWithBean(String sno) {
        String sql = "select * from student where s_no = :sno";
        Student student = new Student();
        student.setSno(sno);
        BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(student);
        List<Student> studentList = new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource())).query(sql, parameterSource, new StudentMapper());
        if (CollectionUtils.isEmpty(studentList)) {
            return null;
        } else {
            return studentList.get(0);
        }
    }

    @Override
    public Student queryStudentBySnoWithMap(String sno) {
        String sql = "select * from student where s_no = :sno";
        HashMap<String, Object> map = new HashMap<>();
        map.put("sno", sno);
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource(map);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(Objects.requireNonNull(template.getDataSource()));
        List<Student> students = namedParameterJdbcTemplate.query(sql, mapSqlParameterSource, new StudentMapper());
        if (CollectionUtils.isEmpty(students)) {
            return null;
        } else {
            return students.get(0);
        }
    }
}
